/* ********************************************************************************************
   Ric - 14/08/2019                                                     
 **********************************************************************************************/
-- Function: pre_obtenerprecioarticuloinfo(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, character, integer)

-- DROP FUNCTION pre_obtenerprecioarticuloinfo(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, character, integer);

CREATE OR REPLACE FUNCTION pre_obtenerprecioarticuloinfo(IN character varying, IN integer, IN character varying, IN character varying, IN character varying, IN character varying, IN character varying, IN integer, IN character varying, IN character varying, IN character varying, IN character varying, IN character varying, IN character varying, IN character, IN integer, OUT precio numeric, OUT infoprecio character varying)
  RETURNS SETOF record AS
$BODY$ 
DECLARE 
vble 		ALIAS FOR $1; 
id_ART 		ALIAS FOR $2; 
fechaEntrada 	ALIAS FOR $3; 
Ctxt_TIPOPREPAGA ALIAS FOR $4; 
Ctxt_CODPREPAG   ALIAS FOR $5; 
Ctxt_TIPOPADA    ALIAS FOR $6; 
Ctxt_CODPADR     ALIAS FOR $7; 
Ctxt_IDPAD       ALIAS FOR $8; 
Ctxt_TIPOARTA    ALIAS FOR $9; 
Ctxt_CODARTR     ALIAS FOR $10; 
Ctxt_FECHAINI    ALIAS FOR $11;  
Ctxt_FECHAFIN    ALIAS FOR $12;  
Ctxt_CODSUC      ALIAS FOR $13;	 
valorNUMERAL_CANT ALIAS FOR $14; 
codambtur        ALIAS FOR $15;  
nrocierre        ALIAS FOR $16;  
--=== parametros de salida === 
pout_precio      ALIAS FOR $17; 
pout_infoprecio  ALIAS FOR $18; 
--=== auxiliares === 
pre_decimales	INTEGER; 
pre_metodoRedondeo VARCHAR; 
formulaNumerica VARCHAR; 
pre_codprered VARCHAR; 
pre_valormonedamenor NUMERIC; 
precioArt	NUMERIC; 
infoPreXArt     VARCHAR; 
BEGIN 
SELECT digPrec, calcPrec,  codprered, valormonedamenor 
INTO pre_decimales, pre_metodoRedondeo,  pre_codprered, pre_valormonedamenor 
FROM pre WHERE (cod = vble); 
infoPreXArt := ''; 

-- Analizo tema IVA cero para consumidores finales
if (vble = 'IVA') AND
   (SELECT CODPADIVA FROM PAD WHERE ID = Ctxt_IDPAD) = 'F' AND
   EXISTS (SELECT * FROM ARTC WHERE TIPOARTA = 'IVA' AND CODARTR = 'IVA0' AND IDART = id_ART)
THEN
   formulaNumerica := '';
   infoPreXArt     := 0.00;
ELSE
IF (vble = 'IVAG') THEN
   formulaNumerica := pre_ObtenerPrecio(cast('IVA' as varchar),id_ART,cast(NOW() as varchar),cast('a0' as varchar));
   infoPreXArt := pre_ObtenerPrecio(cast('IVA' as varchar),id_ART,cast(NOW() as varchar),cast('a0' as varchar));
ELSE

SELECT * FROM pre_ObtenerFormulaInfo(vble, id_ART, 
fechaEntrada, Ctxt_TIPOPREPAGA, Ctxt_CODPREPAG, 
Ctxt_TIPOPADA, Ctxt_CODPADR, Ctxt_IDPAD, Ctxt_TIPOARTA, Ctxt_CODARTR, 
Ctxt_FECHAINI, Ctxt_FECHAFIN, Ctxt_CODSUC, 
CodAmbTur, NroCierre, infoPreXArt) 
INTO formulaNumerica, infoPreXArt;

  END IF;
END IF;
 
IF (COALESCE(formulaNumerica, '') <> '') THEN 
precioArt := pre_Eval(formulaNumerica, valorNUMERAL_CANT); 
ELSE 
precioArt := 0.0000;  -- (-999999999); 
END IF; 
/* 
Redondear el valor de precio calculado: 
1) segun pre.digprec 
2) segun precios sicologicos (si pre.codprered <> NULL) 
3) segun valor menor valido para la moneda (si pre.valormonedamenor <> NULL) 
*/ 
IF pre_metodoRedondeo = 'T' THEN 
precioArt := pre_TruncarValor(precioArt, pre_decimales); 
ELSIF pre_metodoRedondeo = 'RS' THEN  
precioArt := pre_RedondearSupValor(precioArt, pre_decimales); 
ELSIF pre_metodoRedondeo = 'R' THEN 
precioArt := pre_RedondearValor(precioArt, pre_decimales); 
ELSIF pre_metodoRedondeo = '99' THEN 
precioArt := pre_Redondear99Valor(precioArt, pre_decimales); 
ELSIF pre_metodoRedondeo = '50' THEN 
precioArt := pre_Redondear50Valor(precioArt, pre_decimales); 
END IF; 
-- ==== Si define precios sicologicos para la variable de precios (pre.codprered <> NULL) ==== 
IF pre_codprered IS NOT NULL THEN 
precioArt  := pre_ObtenerPrecioSicologico(precioArt, pre_codprered); 
END IF; 
-- ==== Si define valor menor de moneda (pre.valormonedamenor <> NULL) ==== 
IF pre_valormonedamenor IS NOT NULL THEN 
precioArt := pre_redondearvalormoneda(precioArt, pre_valormonedamenor, pre_metodoRedondeo); 
END IF; 
IF precioArt IS NULL THEN 
precioArt := 0.0000; 
END IF; 
SELECT CAST(precioArt AS NUMERIC (16, 4)), infoPreXArt INTO pout_precio, pout_infoprecio; 
RETURN NEXT; --agrega los datos al conjunto resultado de salida 
RETURN; 
END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION pre_obtenerprecioarticuloinfo(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, character, integer)
  OWNER TO abacouser;

-- Function: pre_obtenervalorinfo(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, integer)

-- DROP FUNCTION pre_obtenervalorinfo(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, integer);

CREATE OR REPLACE FUNCTION pre_obtenervalorinfo(IN character varying, IN integer, IN character varying, IN character varying, IN character varying, IN character varying, IN character varying, IN integer, IN character varying, IN character varying, IN character varying, IN character varying, IN character varying, IN character varying, IN integer, OUT character varying, OUT character varying)
  RETURNS SETOF record AS
$BODY$
DECLARE
vble 		 ALIAS FOR $1;
id_ART		 ALIAS FOR $2;
fechaEntrada	 ALIAS FOR $3;
Ctxt_TIPOPREPAGA ALIAS FOR $4;
Ctxt_CODPREPAG   ALIAS FOR $5;
Ctxt_TIPOPADA    ALIAS FOR $6;
Ctxt_CODPADR     ALIAS FOR $7;
Ctxt_IDPAD       ALIAS FOR $8;
Ctxt_TIPOARTA    ALIAS FOR $9;
Ctxt_CODARTR     ALIAS FOR $10;
Ctxt_FECHAINI    ALIAS FOR $11;
Ctxt_FECHAFIN    ALIAS FOR $12;
Ctxt_CODSUC      ALIAS FOR $13;
codambtur        ALIAS FOR $14;
nrocierre        ALIAS FOR $15;
--=== parametros de salida
id_PRE		INTEGER;
prexartporfechaPRE VARCHAR;
escotizacionPRE VARCHAR;
formulaPRE VARCHAR;
codvalmonPRE VARCHAR;
pout_Formula      ALIAS FOR $16;
pout_InfoFormula  ALIAS FOR $17;
valorPRExART	VARCHAR;
infoPRExART     VARCHAR;
valorFormula	VARCHAR;
infoFormula     VARCHAR; 
valorRUBROSEC	VARCHAR;
valorRUBROPPAL	VARCHAR;
valorPRECIOESP	VARCHAR;
valorCTZ 	VARCHAR;
BEGIN
IF (vble IN ('#PULTV','#PULTC')) THEN
BEGIN
  valorFormula := pre_obtenerprecioultimacompraventa(vble, id_art, Ctxt_IDPAD, fechaentrada);
END;
ELSE
BEGIN
  --recupero datos de la variable de precio
  SELECT id, prexartporfecha, escotizacion, formula, codvalmon  FROM pre WHERE (cod = vble)
  INTO id_PRE, prexartporfechaPRE, escotizacionPRE, formulaPRE, codvalmonPRE;
  --recupero valor/formula de Precios Especiales
  valorPRECIOESP = pre_ObtenerPrecioEspecial(id_PRE, id_ART, Ctxt_TIPOPREPAGA, Ctxt_CODPREPAG, Ctxt_TIPOPADA, Ctxt_CODPADR, Ctxt_IDPAD, Ctxt_TIPOARTA, Ctxt_CODARTR, Ctxt_FECHAINI, Ctxt_FECHAFIN, Ctxt_CODSUC);
  --si el articulo no tiene precio especial
  IF (valorPRECIOESP = '') OR (valorPRECIOESP IS NULL) THEN
  BEGIN
    --recupero el valor en prexart
    --================================================================
    -- SELECT COALESCE(CAST(valor AS VARCHAR), '') INTO valorPRExART FROM prexart WHERE (idart = id_ART) AND (idpre = id_PRE);
    IF prexartporfechaPRE = 'S' THEN
    BEGIN
      SELECT INTO valorPRExART, infoPRExART
      COALESCE(CAST(valor AS VARCHAR), ''), 
      COALESCE(to_char(fechaprecio, 'DD/MM/YYYY'), '') ||
      COALESCE('-'|| CAST(codambturprecio AS VARCHAR), '') || 
      COALESCE('('|| CAST(nrocierreprecio AS VARCHAR)||')', '')          
      FROM prexart 
      WHERE (idart = id_ART) AND (idpre = id_PRE)
      AND COALESCE(fechaprecio, '-INFINITY') <= CAST (COALESCE(fechaentrada, 'INFINITY') AS TIMESTAMP)
      AND COALESCE(codambturPrecio, '') <= COALESCE(codambtur, 'zzzzzzzzzz')
      AND COALESCE(NroCierrePrecio, 0)  <= COALESCE(nrocierre, 9999)
      ORDER BY fechaprecio DESC NULLS LAST, 
      codambturprecio DESC NULLS LAST,
      nrocierreprecio DESC NULLS LAST
      LIMIT 1;
    END;
    ELSE
      SELECT INTO valorPRExART, infoPRExART
      COALESCE(CAST(valor AS VARCHAR), ''), CAST('' AS VARCHAR)
      FROM prexart 
      WHERE (idart = id_ART) AND (idpre = id_PRE)
      AND fechaprecio IS NULL
      AND codambturPrecio IS NULL
      AND NroCierrePrecio  IS NULL;
    END IF;
    --==============================================================
    --si el articulo no esta en prexart
    IF ( COALESCE(valorPRExART, '') = '' ) THEN
    BEGIN
      --recupero valor/formula en prexrub del Arbol Secundario
      SELECT COALESCE(prexrubSEC.formula, '') INTO valorRUBROSEC FROM artc
      INNER JOIN prexrub prexrubSEC ON  artc.tipoarta = prexrubSEC.tipoarta AND  artc.codartr || '.' LIKE prexrubSEC.codartr || '.%'
      WHERE (artc.idart = id_ART) AND (idpre = id_PRE)
      ORDER BY pre_CantOcurrencias(prexrubSEC.codartr, '.') DESC, prexrubSEC.codartr DESC LIMIT 1;
      --si el articulo no esta en un arbol secundario
      IF (valorRUBROSEC = '') OR (valorRUBROSEC IS NULL) THEN
      BEGIN
      --recupero el valor/formula en prexrub del Arbol Principal
        SELECT COALESCE(prexrubPPAL.formula, '') INTO valorRUBROPPAL FROM art
        INNER JOIN prexrub prexrubPPAL ON art.tipoarta = prexrubPPAL.tipoarta  AND art.codartr || '.'  LIKE prexrubPPAL.codartr || '.%'
        WHERE (art.id = id_ART) AND (idpre = id_PRE)
        ORDER BY pre_CantOcurrencias(prexrubPPAL.codartr, '.') DESC,  prexrubPPAL.codartr DESC LIMIT 1;
      --si el articulo no esta en el arbol ppal con vbles propias
        IF (valorRUBROPPAL = '') OR (valorRUBROPPAL IS NULL) THEN
        BEGIN
          --recupero el valor/formula de pre o valctz segun sea o no cotizacion de moneda
          IF escotizacionPRE = 'S' THEN
          BEGIN
            SELECT CAST(valctz.cotizacion AS VARCHAR) INTO valorCTZ
            FROM valctz 
            WHERE valctz.codvalmon = codvalmonPRE AND ( CAST(DATE(valctz.vigencia) AS VARCHAR) <= fechaEntrada )
            ORDER BY valctz.vigencia DESC LIMIT 1;
            valorFormula := valorCTZ;
          END;
          ELSE
            valorFormula := formulaPRE;
          END IF;
        END;
        ELSE --el articulo esta en el arbol ppal
          valorFormula := valorRUBROPPAL;
        END IF;
      END;
      ELSE --el articulo esta en un arbol secundario
        valorFormula := valorRUBROSEC;
      END IF;
    END;
    ELSE --el articulo esta en prexart
      valorFormula := valorPRExART;
    infoFormula  := InfoPREXART;
    END IF;
  END;
  ELSE --el articulo tiene precio especial
    valorFormula := valorPRECIOESP;
  END IF;
END;
END IF;

if (vble = 'IVA') AND
   (SELECT CODPADIVA FROM PAD WHERE ID = Ctxt_IDPAD) = 'F' AND
   EXISTS (SELECT * FROM ARTC WHERE TIPOARTA = 'IVA' AND CODARTR = 'IVA0' AND IDART = id_ART)
THEN
   valorFormula := 0.00;
ELSE
  IF (vble = 'IVAG') THEN
     valorFormula := pre_ObtenerPrecio(cast('IVA' as varchar),id_ART,cast(NOW() as varchar),cast('a0' as varchar));
  END IF;
END IF;

  
--devolver el valor/formula entre ()
valorFormula := '(' || valorFormula || ')';
--sacar enters, tabs y returns del valor/formula recuperado
valorFormula := REPLACE(valorFormula, chr(13)||chr(10), ''); 	--retorno de carro + nueva linea
valorFormula := REPLACE(valorFormula, chr(9),''); 		--tab
valorFormula := REPLACE(valorFormula, chr(10), ''); 		--nueva linea ( chr(10) = '\n' )
SELECT * FROM pre_ObtenerFormulaInfo(valorFormula, id_ART, fechaEntrada, Ctxt_TIPOPREPAGA, Ctxt_CODPREPAG, Ctxt_TIPOPADA, Ctxt_CODPADR, Ctxt_IDPAD, 
                                     Ctxt_TIPOARTA, Ctxt_CODARTR, Ctxt_FECHAINI, Ctxt_FECHAFIN, Ctxt_CODSUC, CodAmbTur, NroCierre, infoFormula)
INTO valorFormula, infoFormula;
pout_Formula     := valorformula;
pout_InfoFormula := infoFormula;
RETURN NEXT; --agrega los datos al conjunto resultado de salida
RETURN;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION pre_obtenervalorinfo(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, integer)
  OWNER TO abacouser;


-- ===========================
select  aaVersion('411.01');
-- ===========================

